USE [GameSession]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Users](
	[USERID] [int] PRIMARY KEY IDENTITY,
	[extID] [int] NOT NULL,
	[UserName] [varchar](50) NOT NULL,
	[CDATE] [datetime] NOT NULL DEFAULT(getdate()))
GO
CREATE TABLE [dbo].[Rules](
	[RuleSetID] [int] PRIMARY KEY IDENTITY,
	[OptionID] [int] NOT NULL,
	[userID] [int] NOT NULL,
	[Value] [varchar](max) NOT NULL,
	[Description] [varchar](max) NOT NULL,
	[CDATE] [datetime] NOT NULL DEFAULT(getdate()))
GO
CREATE TABLE [dbo].[RoomTransactions](
	[TransactionID] [int] PRIMARY KEY IDENTITY,
	[roomID] [int] NOT NULL,
	[userID] [int] NOT NULL,
	[ruleSetID] [int] NOT NULL,
	[trx] [varchar](10) NOT NULL,
	[Attrbutes] [varchar](max) NOT NULL,
	[CDATE] [datetime] NOT NULL DEFAULT(getdate())
	)
GO
CREATE TABLE [dbo].[Rooms](
	[RoomID] [int] PRIMARY KEY IDENTITY,
	[userID] [int] NOT NULL,
	[ruleSetID] [int] NOT NULL,
	[Attributes] [varchar](max) NOT NULL,
	[CDATE] [datetime] NOT NULL DEFAULT(getdate()),
	[Modified] [datetime] NOT NULL DEFAULT(getdate()))
GO
CREATE TABLE [dbo].[Characters](
	[CharID] [int] PRIMARY KEY IDENTITY,
	[userID] [int] NOT NULL,
	[CharacterName] [varchar](50) NOT NULL,
	[Attributes] [varchar](max) NOT NULL,
	[CDATE] [datetime] NOT NULL DEFAULT(getdate()),
	[Modified] [datetime] NOT NULL DEFAULT(getdate()))
GO

CREATE TRIGGER [dbo].[UpdateRooms] 
ON [dbo].[Rooms]
FOR UPDATE 
AS 
BEGIN 
    IF NOT UPDATE([Modified]) 
        UPDATE [dbo].[Rooms] SET [Modified]=GETDATE() 
        WHERE [RoomID] IN (SELECT [RoomID] FROM inserted) 
END 
GO
CREATE TRIGGER [dbo].[UpdateCharacters] 
ON [dbo].[Characters]
FOR UPDATE 
AS 
BEGIN 
    IF NOT UPDATE([Modified]) 
        UPDATE [dbo].[Characters] SET [Modified]=GETDATE() 
        WHERE [CharID] IN (SELECT [CharID] FROM inserted) 
END 
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Rules]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([USERID])
GO
ALTER TABLE [dbo].[RoomTransactions]  WITH CHECK ADD FOREIGN KEY([roomID])
REFERENCES [dbo].[Rooms] ([RoomID])
GO
ALTER TABLE [dbo].[RoomTransactions]  WITH CHECK ADD FOREIGN KEY([ruleSetID])
REFERENCES [dbo].[Rules] ([RuleSetID])
GO
ALTER TABLE [dbo].[RoomTransactions]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([USERID])
GO
ALTER TABLE [dbo].[Rooms]  WITH CHECK ADD FOREIGN KEY([ruleSetID])
REFERENCES [dbo].[Rules] ([RuleSetID])
GO
ALTER TABLE [dbo].[Rooms]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([USERID])
GO
ALTER TABLE [dbo].[Characters]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([USERID])
GO
